package com.ruoyi.dataserver.service.impl;

import com.ruoyi.dataserver.api.dto.StatisticResultDTO;
import com.ruoyi.dataserver.dto.LayerDto;
import com.ruoyi.dataserver.remote.RemoteService;
import com.ruoyi.dataserver.utils.R;
import com.ruoyi.dataserver.vo.LayerStatisticVO;
import com.ruoyi.dataserver.vo.StatisticsDetailVO;
import com.ruoyi.dataserver.api.dto.ScopeDto;
import com.ruoyi.dataserver.api.dto.StatisticDto;
import com.ruoyi.dataserver.service.ISearchTableService;
import com.ruoyi.dataserver.service.ISegmentationService;
import com.ruoyi.dataserver.utils.NumberUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ProjectName intelligent-city
 * @Package com.lab.dataserver.service.impl
 * @ClassName SegmentationServiceImpl
 * @Description 分段统计实现类
 * @Company LAB
 * @Author LXK
 * @Date 2020/7/9 15:30
 * @UpdateUser
 * @UpdateDate
 * @UpdateRemark
 * @Version 1.0.0
 */
@Slf4j
@Component
public class SegmentationServiceImpl implements ISegmentationService {

  @Autowired
  public JdbcTemplate jdbcTemplate;

  @Autowired
  public RemoteService remoteService;

  @Autowired
  public ISearchTableService tableService;
  /**
   * @param statisticDto :
   * @return com.lab.dataserver.api.dto.StatisticResultDTO
   * @Title pipeDiameterSegmentation
   * @Description 管径分段统计
   * @author Lixk
   * @date 2020/7/13 17:06
   * @UpdateUser
   * @UpdateDate
   * @UpdateRemark
   */

  @Override
  public StatisticResultDTO pipeDiameterSegmentation(StatisticDto statisticDto) {
    // 解析图层名作为表名
    String layer = statisticDto.getLayerList()[0];
    String tableName = tableService.searchLineTable(layer.split("_")[0]);  // 组成管线表名
    log.info("获得图层名称{}", tableName);
    // 获得坐标范围
    StringBuilder coordinateSql = new StringBuilder();
    if (statisticDto.getQueryType().equals("scope")) {
      coordinateSql = lineObtainScopeSql(statisticDto.getScope());
    }
    // 获得分段区间列表
    int span = statisticDto.getSegment().intValue();
    List<List> spanList = NumberUtils.getIntegerIntervalBySpan(0, 2000, span);
    StatisticResultDTO resultDTO = new StatisticResultDTO();
    List statisticsData = new ArrayList();
    // 图层汇总统计
    LayerStatisticVO layerStatisticVO = new LayerStatisticVO();
    // 获得图层列表
    layerStatisticVO.setLayerName(obtainLayerNameList(layer));
    //layerStatisticVO.setLayerName(getLayerNameList(layer));
    layerStatisticVO.setLayer(layer);
    layerStatisticVO.setFieldName("合计");
    StringBuilder layerSql = new StringBuilder(
        "SELECT COUNT(*) count , SUM(T.CD) length  FROM \"" + tableName
            + "\" T WHERE T.GJ <> ' '  ");
    if (coordinateSql.length() > 0) {
      layerSql.append(coordinateSql);
    }
    List<Map<String, Object>> layerMapList = jdbcTemplate.queryForList(layerSql.toString());
    Long layerCount = (Long) layerMapList.get(0).get("count");
    layerStatisticVO.setCount(layerCount);
    BigDecimal layerLength = (BigDecimal) layerMapList.get(0).get("length");
    if (layerLength != null) {
      layerStatisticVO.setLength(layerLength.doubleValue());
    }

    List detailList = new ArrayList();
    layerStatisticVO.setDetailList(detailList);
    // 分段统计
    StringBuilder finalCoordinateSql = coordinateSql;
    spanList.forEach(child -> {
      int start = (int) child.get(0);
      int end = (int) child.get(1);
      StringBuilder sql = new StringBuilder(
          "SELECT COUNT(*) count , SUM(T.CD) length  FROM \"" + tableName
              + "\" T WHERE T.GJ <> ' '  ");
      sql.append(" AND cast(REGEXP_SUBSTR(T.GJ,'[^X]+',1,1,'i') as varchar) > " + start + "");
      sql.append(" AND cast(REGEXP_SUBSTR(T.GJ,'[^X]+',1,1,'i') as varchar) <= " + end + "");
      if (finalCoordinateSql.length() > 0) {
        sql.append(finalCoordinateSql);
      }
      StatisticsDetailVO vo = new StatisticsDetailVO();
      List<Map<String, Object>> detailMapList = jdbcTemplate.queryForList(sql.toString());
      Long count = (Long) detailMapList.get(0).get("count");
      vo.setCount(count);
      BigDecimal length = (BigDecimal) detailMapList.get(0).get("length");
      if (length != null) {
        vo.setLength(length.doubleValue());
      }
      vo.setFieldName(start + "-" + end);
      detailList.add(vo);
    });
    // 统计最大区间
    StatisticsDetailVO bigVO = new StatisticsDetailVO();
    StringBuilder bigSql = new StringBuilder(
        "SELECT COUNT(*) count , SUM(T.CD) length  FROM \"" + tableName
            + "\" T WHERE T.GJ <> ' ' AND cast(REGEXP_SUBSTR(T.GJ,'[^X]+',1,1,'i') as varchar) > 2000 ");
    if (coordinateSql.length() > 0) {
      bigSql.append(coordinateSql);
    }
    List<Map<String, Object>> bigMapList = jdbcTemplate.queryForList(bigSql.toString());
    Long bigCount = (Long) bigMapList.get(0).get("count");
    bigVO.setCount(bigCount);
    BigDecimal bigLength = (BigDecimal) bigMapList.get(0).get("length");
    if (bigLength != null) {
      bigVO.setLength(bigLength.doubleValue());
    }
    detailList.add(bigVO);
    bigVO.setFieldName("2000-最长");
    statisticsData.add(layerStatisticVO);
    resultDTO.setTotalCount(layerStatisticVO.getCount());
    resultDTO.setTotalLength(layerStatisticVO.getLength());
    resultDTO.setStatisticsData(statisticsData);
    // 汇总统计
    log.info("结束管径分段统计");
    return resultDTO;
  }

  /**
   * @param layer :
   * @return com.lab.dataserver.utils.R<java.util.List>
   * @Title obtainLayerNameList
   * @Description 查询图层列表
   * @author heff
   * @date 2020/7/21 15:44
   */

  private String obtainLayerNameList(String layer) {
    String label = "";
    String sql = "select value from layername where name = '"+layer+"'";
    RowMapper<LayerDto> tableRowMapper = new BeanPropertyRowMapper<LayerDto>(LayerDto.class);
    List<LayerDto> tableList = jdbcTemplate.query(sql, tableRowMapper);
    if(tableList.size() > 0){
      label = tableList.get(0).getValue();
    }
    return label;
  }

  /**
   * 获取图层中文名称
   * @param layer
   * @return
   */
  private String getLayerNameList(String layer) {

    Map<String,Object>  map=new HashMap<>();
    map.put("JSG_POINT","给水管点");
    map.put("JSG_LINE","给水管线");
    map.put("JSG_LINE_TEXT","给水管线注记");
    map.put("JSG_BOUNDLINE","给水井室边框");
    map.put("SSG_POINT","输水管点");
    map.put("SSG_LINE","输水管线");

    map.put("SSG_LINE_TEXT","输水管线注记");
    map.put("SSG_BOUNDLINE","输水井室边框");
    map.put("XSG_POINT","消防水管点");
    map.put(" XSG_LINE","消防水管线");
    map.put("XSG_LINE_TEXT","消防水管线注记");
    map.put("JSG_BOUNDLINE","消防水井室边框");
System.out.println("++++++++++++++++++++++++++++++++++"+map.get(layer));
    return (String) map.get(layer);
  }

  /**
   * @param scopeDto :
   * @return java.lang.StringBuilder
   * @Title obtainScopeSql
   * @Description 拼接按选取范围查询脚本
   * @author heff
   * @date 2020/8/21 10:46
   * @UpdateUser
   * @UpdateDate
   * @UpdateRemark
   */

  private StringBuilder lineObtainScopeSql(ScopeDto scopeDto) {
    StringBuilder coordinateSql = new StringBuilder();
    if (scopeDto != null) {
      Double xmin = scopeDto.getXmin();
      Double xmax = scopeDto.getXmax();
      Double ymin = scopeDto.getYmin();
      Double ymax = scopeDto.getYmax();
      coordinateSql.append(" AND  LEAST(t.s_x,t.e_x) >=" + xmin);
      coordinateSql.append(" AND LEAST(t.s_y,t.e_y) >=" + ymin);
      coordinateSql.append(" AND GREATEST(t.s_x,t.e_x) <" + xmax);
      coordinateSql.append(" AND GREATEST(t.s_y,t.e_y) <" + ymax);
    }
    return coordinateSql;
  }
  /**
   * @param statisticDto :
   * @return com.lab.dataserver.api.dto.StatisticResultDTO
   * @Title pipeSegmentationDeep
   * @Description 埋深统计
   * @author Lixk
   * @date 2020/7/13 17:07
   * @UpdateUser
   * @UpdateDate
   * @UpdateRemark
   */

  @Override
  public StatisticResultDTO pipeSegmentationDeep(StatisticDto statisticDto) {
    // 解析图层名作为表名
    String layer = statisticDto.getLayerList()[0];
    String tableName = tableService.searchLineTable(layer.split("_")[0]);  // 组成管线表名
    log.info("获得图层名称{}", tableName);
    // 获得坐标范围
    StringBuilder coordinateSql = new StringBuilder();
    if (statisticDto.getQueryType().equals("scope")) {
      coordinateSql = lineObtainScopeSql(statisticDto.getScope());
    }
    // 获得分段区间列表
    double span = statisticDto.getSegment();
    List<List> spanList = NumberUtils.getDoubleIntervalBySpan(0.00, 20, span);
    StatisticResultDTO resultDTO = new StatisticResultDTO();
    List statisticsData = new ArrayList();
    // 图层汇总统计
    LayerStatisticVO layerStatisticVO = new LayerStatisticVO();
    // 获得图层列表
    //String aa = obtainLayerNameList(layer);
    layerStatisticVO.setLayerName(obtainLayerNameList(layer));
    layerStatisticVO.setLayer(layer);
    layerStatisticVO.setFieldName("合计");
    StringBuffer filterSql = new StringBuffer(
        "SELECT LEAST(T.QDMS,T.ZDMS) deep,T.CD shapeLen FROM \"" + tableName
            + "\" T WHERE (T.QDMS <> ' ' AND T.ZDMS <> ' ')");
    if (coordinateSql.length() > 0) {
      filterSql.append(coordinateSql);
    }
    StringBuffer layerSql = new StringBuffer(" SELECT  COUNT(*) count,  SUM(shapeLen) length  " +
        " FROM ( " + filterSql + ") a");
    List<Map<String, Object>> layerMapList = jdbcTemplate.queryForList(layerSql.toString());
    Long layerCount = (Long) layerMapList.get(0).get("count");
    layerStatisticVO.setCount(layerCount);
    BigDecimal layerLength = (BigDecimal) layerMapList.get(0).get("length");
    if (layerLength != null) {
      layerStatisticVO.setLength(layerLength.doubleValue());
    }

    List detailList = new ArrayList();
    layerStatisticVO.setDetailList(detailList);
    // 分段统计
    spanList.forEach(child -> {
      double start = (double) child.get(0);
      double end = (double) child.get(1);
      StringBuffer sql = new StringBuffer(" SELECT  COUNT(*) count ,  SUM(shapeLen) length  " +
          " FROM (" + filterSql + ") a" + " WHERE 1=1");
      sql.append(" AND cast(deep as float) > " + start + "");
      sql.append(" AND cast(deep as float) <= " + end + "");
      StatisticsDetailVO vo = new StatisticsDetailVO();
      List<Map<String, Object>> detailMapList = jdbcTemplate.queryForList(sql.toString());
      Long count = (Long) detailMapList.get(0).get("count");
      vo.setCount(count);
      BigDecimal length = (BigDecimal) detailMapList.get(0).get("length");
      if (length != null) {
        vo.setLength(length.doubleValue());
      }
      vo.setFieldName(start + "-" + end);
      detailList.add(vo);
    });
    // 统计最大区间
    StatisticsDetailVO bigVO = new StatisticsDetailVO();
    StringBuffer bigSql = new StringBuffer(" SELECT  COUNT(*) count ,  SUM(shapeLen) length  " +
        " FROM (" + filterSql + ") a WHERE cast(deep as float) >20.00 ");
    List<Map<String, Object>> bigMapList = jdbcTemplate.queryForList(bigSql.toString());
    Long bigCount = (Long) bigMapList.get(0).get("count");
    bigVO.setCount(bigCount);
    BigDecimal bigLength = (BigDecimal) bigMapList.get(0).get("length");
    if (bigLength != null) {
      bigVO.setLength(bigLength.doubleValue());
    }
    detailList.add(bigVO);
    bigVO.setFieldName("20.0-最深");
    statisticsData.add(layerStatisticVO);
    resultDTO.setTotalCount(layerStatisticVO.getCount());
    resultDTO.setTotalLength(layerStatisticVO.getLength());
    resultDTO.setStatisticsData(statisticsData);
    // 汇总统计
    log.info("结束埋深分段统计");
    return resultDTO;
  }
}
